libname Input "Input Data";
libname Output "Output Data";

proc sort data = Input.BoardEx_Comp out = BX_Comp (keep = BoardID TICKER CIKCODE) nodupkey; by BoardID TICKER CIKCODE; run;

data BX_Comp;
	set BX_Comp;
	if TICKER = "" and CIKCODE = "" then delete;
run;
	
proc sort data = BX_Comp; by BoardID descending TICKER; run;

data BX_Comp;
	set BX_Comp;
	if BoardID = lag(BoardID) and CIKCODE = lag(CIKCODE) and TICKER = "" then delete;
run;
	
proc sort data = BX_Comp; by BoardID descending CIKCODE; run;

data BX_Comp;
	set BX_Comp;
	if BoardID = lag(BoardID) and TICKER = lag(TICKER) and CIKCODE = "" then delete;
	CIK = put(input(CIKCODE,12.),z10.);
run;


proc sort data = BX_Comp nodupkey; by BoardID CIK; run;





data Compustat;
	set Input.Compustat_2022 (keep = GVKEY CIK TIC);
	if CIK = "" then delete;
run;

proc sort data = Compustat nodupkey; by GVKEY CIK; run;



proc sort data = BX_Comp; by CIK; run;
proc sort data = Compustat; by CIK; run;

data BX_Comp;
	merge BX_Comp (in = a) Compustat (in = b drop = TIC where = (CIK ~= ""));
	by CIK;
	if a = 1;
run;

data BX_Comp2;
	set BX_Comp;
	if GVKEY ~= "" then delete;
	if TICKER = "" then delete;
run;

proc sort data = BX_Comp2; by TICKER; run;
proc sort data = Compustat; by TIC; run;

data BX_Comp2;
	merge BX_Comp2 (in = a drop = GVKEY) Compustat (in = b drop = CIK rename = (TIC = TICKER) where = (TICKER ~= ""));
	by TICKER;
	if a = 1;
	if GVKEY = "" then delete;
run;

proc append base = BX_Comp data = BX_Comp2; run;

data BX_Comp;
	set BX_Comp;
	if GVKEY = "" then delete;
run;



data Comp;
	set Comp.Compustat_2022 (keep = LPERMCO GVKEY DATADATE NAICS PSTKRV PSTKL UPSTK SEQ TXDITC XAD CSHR SALE REVT COGS XSGA TIE AT
																CHE DLC DLTT TXDB CEQ CSHO PRCC_F PPENT DP IB XRD CAPX PPEGT NI OPITI DP DD1);
	Year = year(DATADATE);

	Prefer = PSTKRV;
    if Prefer = . then Prefer = PSTKL;
    if Prefer = . then Prefer = UPSTK;
    BookEqty = SEQ - Prefer + TXDITC;
    if BookEqty = . then BookEqty = SEQ - prefer;
    if BookEqty = . then BookEqty = SEQ + TXDITC;
    if BookEqty = . then BookEqty = SEQ;
    if BookEqty < 0 then BookEqty = .;

	Mkt_Cap = abs(PRCC_F*CSHO);
run;

proc sort data = Comp; by GVKEY Year; run;

proc means data = Comp noprint;
	by GVKEY Year;
	var Mkt_Cap AT SALE BookEqty NI;
	output out = Comp_Unique sum = Mkt_Cap AT SALE BookEqty NI;
run;

data Comp_Unique;
	set Comp_Unique;
	Year = Year + 1;

	lag_AT = lag(AT);
	if GVKEY ~= lag(GVKEY) then lag_AT = .;
	if Year ~= lag(Year)+1 then lag_AT = .;
	lag_SALE = lag(SALE);
	if GVKEY ~= lag(GVKEY) then lag_SALE = .;
	if Year ~= lag(Year)+1 then lag_SALE = .;

	Q = (AT - bookeqty + Mkt_Cap)/AT;
	ROA = NI/lag_AT;
	Sales_Growth = SALE/lag_SALE - 1;

	if GVKEY = "" then delete;
	keep GVKEY Year AT Q ROA Sales_Growth;
run;






proc sort data = Output.Contracts_Analysis out = Contracts_Analysis; by CompanyID AgencyID Year; run;
proc sort data = BX_Comp; by BoardID; run;

data Contracts_Analysis2;
	merge Contracts_Analysis (in = a) BX_Comp (in = b keep = BoardID GVKEY rename = (BoardID = CompanyID));
	by CompanyID;
	if a = 1 and b = 1;
run;

proc sort data = Contracts_Analysis2; by GVKEY Year; run;
proc sort data = Comp_Unique; by GVKEY Year; run;

data Contracts_Analysis2;
	merge Contracts_Analysis2 (in = a) Comp_Unique (in = b);
	by GVKEY Year;
	if a = 1 and b = 1;
run;

proc export data = Contracts_Analysis2 outfile = "Output Data\contracts_analysis_public.dta"
	dbms = dta replace;
run;
